package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.Grade;
import com.xmy.cultivate.entity.LeaveTeacherStudent;
import com.xmy.cultivate.entity.RecordCourse;
import com.xmy.cultivate.entity.StudentGrade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.respons.SchoolDateTime;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 学生所在班级与课程（分班表） Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-03-20
 */
public interface StudentGradeMapper extends BaseMapper<StudentGrade> {


    /**
     * 获取最后一次分班数据
     *
     * @return
     */
    @Select("select * from student_grade where school_id =#{schoolId} and subjects_id = #{subjectsId} and student_id = #{studentId} and year_part=#{yearPart} and quarter_num=#{quarterNum} and deleted=0 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForStudent(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);


    /**
     * 获取当前学期已分班的科目信息
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @return
     */
    @Select({
            "<script>",
            "select * from student_grade as t1 INNER JOIN (select MAX(id) as id from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and student_id = ${studentId} and lesson_type = ${lessonType}",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and subjects_id = #{subjectsId}",
            "</when>",
            "and status &lt;&gt;3 and deleted=0 GROUP BY student_id,subjects_id) as t2 on t1.id = t2.id",
            "</script>"
    })
    public List<StudentGrade> getReadingData(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);


    @Update("update student_grade set deleted = ${deleted} where id = ${id}")
    public Integer updateDeleted(@Param("id") Long id,@Param("deleted") Integer deleted);

    /**
     * 获取最后一次分班在读信息
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select *,getStaffNameForId(teacher_id) as teacher_name from student_grade_view where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and subjects_id = ${subjectsId} and student_id = ${studentId} and lesson_type =1 and reading_status =1 and deleted =0 ORDER BY id desc limit 1")
    public RecordCourse getLastReadingGrade(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId);



    @Select("select count(id) from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and subjects_id = ${subjectsId} and student_id = ${studentId} and lesson_type = ${lessonType} and is_presort in(${presortStr}) and deleted =0 and `status`<>3")
    public Integer getPresortCount(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType,@Param("presortStr") String presortStr);


    /**
     * 获取班级自动分班的学生
     * @param gradeId
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "* ",
            "FROM",
            "student_grade AS t2 ",
            "WHERE",
            "t2.grade_id = ${gradeId} ",
            "AND t2.reading_status IN ( 0, 1 ) ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3",
            "and t2.add_type = 2",
            "ORDER BY scheduling_score,id desc",
            "</script>"})
    public List<StudentGrade> getSchedulingNewStudent(@Param("gradeId") Long gradeId);

    @Select({"<script>",
            "SELECT",
                    "t2.* ",
                    "FROM",
                    "student_grade AS t2",
                    "INNER JOIN grade AS t1 ON t2.grade_id = t1.id ",
                    "WHERE",
                    "t2.grade_id = ${gradeId} ",
                    "AND t2.reading_status IN ( 0, 1 ,4) ",
                    "AND t2.use_course_count = 0",
                    "AND t2.deleted = 0 ",
                    "AND t2.`status` &lt;&gt; 3 ",
                    "AND t2.add_type = 2 ",
                    "AND t2.scheduling_type = 0 ",
                    "AND t2.scheduling_score &lt; t1.start_score ",
                    "AND t2.scheduling_score &lt; ${schedulingScore} ",
                    "ORDER BY",
                    "scheduling_score ASC",
            "</script>"})
    public List<StudentGrade> getSchedulingNewStudentForScore(@Param("gradeId") Long gradeId,
                                                              @Param("schedulingScore") BigDecimal schedulingScore);

    @Select({"<script>",
            "SELECT",
                    "SUM( use_course_count ) AS use_course_count ",
                    "FROM",
                    "student_grade ",
                    "WHERE",
                    "year_part = ${yearPart} ",
                    "AND quarter_num = ${quarterNum} ",
                    "AND school_id = ${schoolId} ",
                    "AND subjects_id = ${subjectsId} ",
                    "AND student_id = ${studentId} ",
                    "AND lesson_type = ${lessonType} ",
                    "AND deleted = 0 ",
                    "AND `status` &lt;&gt;3",
            "</script>"})
    public Integer getUseCourseCount(@Param("yearPart") Integer yearPart,
                                     @Param("quarterNum") Integer quarterNum,
                                     @Param("schoolId") Long schoolId,
                                     @Param("subjectsId") Long subjectsId,
                                     @Param("studentId") Long studentId,
                                     @Param("lessonType") Integer lessonType
                                     );

    /**
     * 获取分班的上课时间(已排)
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param studentId
     * @param lessonType
     * @param unSubjectsId 排除的科目
     * @param weekDates 周几，学期班使用
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.grade_id,",
            "t2.time_start,",
            "t2.time_end ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id and t2.is_auto_scheduling =1",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.reading_status in(0,1) ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                //"AND t1.lesson_type = ${lessonType} ",
            "</when>",
            "<when test='unSubjectsId!=null and unSubjectsId!=&apos;&apos; '>",
                "AND t2.subjects_id &lt;&gt; ${unSubjectsId} ",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos; '>",
                "AND t2.week_dates = ${weekDates} ",
            "</when>",
            "<when test='schedulingType!=null and schedulingType!=&apos;&apos; '>",
                "AND t2.scheduling_type = ${schedulingType} ",
            "</when>",
            "AND t1.student_id = ${studentId} ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.deleted = 0",
            "GROUP BY t2.time_start,t2.time_end",
            "ORDER BY t2.time_start,t2.time_end asc",
            "</script>"})
    public List<Grade> getStudentGradeTime(@Param("yearPart") Integer yearPart,
                                           @Param("quarterNum") Integer quarterNum,
                                           @Param("schoolId") Long schoolId,
                                           @Param("studentId") Long studentId,
                                           @Param("lessonType") String lessonType,
                                           @Param("unSubjectsId") String unSubjectsId,
                                           @Param("weekDates") String weekDates,
                                           @Param("schedulingType") String schedulingType
    );


    //获取冲突的排课
    @Select({"<script>",
            "SELECT",
                      "t1.school_id,",
                      "t1.student_id,",
                      "t1.subjects_id,",
                      "t1.year_part,",
                      "t1.quarter_num,",
                      "t1.grade_id,",
                      "t2.time_start,",
                      "t2.time_end ",
                    "FROM",
                      "student_grade AS t1",
                      "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
                    "WHERE",
                      "t1.year_part = ${yearPart} ",
                      "AND t1.quarter_num = ${quarterNum} ",
                      "AND t1.reading_status in(0,1) ",
                      "AND t1.lesson_type = ${lessonType} ",
                      "AND t1.student_id = ${studentId} ",
                      "AND t1.school_id = ${schoolId} ",
                      "AND t1.`status` &lt;&gt; 2 ",
                      "AND t1.deleted = 0 ",
                        "<when test='weekDates!=null and weekDates!=&apos;&apos; '>",
                            "and t2.week_dates = #{weekDates}",
                        "</when> ",
                        "<when test='schedulingType!=null and schedulingType!=&apos;&apos; '>",
                            "and t2.scheduling_type = #{schedulingType}",
                        "</when> ",
                      "<when test='schoolDateTimeList != null and schoolDateTimeList.size() > 0'> ",
                          "AND (",
                             "<foreach collection='schoolDateTimeList' item='item' open='(' separator='or' close=')'>",
                                "(t2.time_start &gt;= #{item.timeStart} AND t2.time_start &lt;= #{item.timeEnd} ) OR ( t2.time_end &gt;= #{item.timeStart} AND t2.time_end &lt;= #{item.timeEnd} ) OR ( t2.time_start &lt;= #{item.timeStart} AND t2.time_end &gt;= #{item.timeEnd}) ",
                             "</foreach>",
                          ")",
                      "</when> ",
    "</script>"})
    public List<StudentGrade> getStudentGradeClash(@Param("yearPart") Integer yearPart,
                                                   @Param("quarterNum") Integer quarterNum,
                                                   @Param("schoolId") Long schoolId,
                                                   @Param("studentId") Long studentId,
                                                   @Param("lessonType") Integer lessonType,
                                                   @Param("schoolDateTimeList") List<SchoolDateTime> schoolDateTimeList,
                                                   @Param("weekDates") String weekDates,
                                                   @Param("schedulingType") String schedulingType
                                                   );

    /**
     * 不冲突时间的班级(可排班级)
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param lessonType
     * @param schoolDateTimeList
     * @param schoolTime
     * @param orderType asc,desc
     * @param noonType 1上午，2下午
     * @param diffType 智能课或者刷题班上课时间 1小于精品课时间，2大于精品课时间
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "t2.id,",
                    "t2.name,",
                    "t2.week_dates,",
                    "t2.school_id,",
                    "t2.subjects_id,",
                    "t2.year_part,",
                    "t2.quarter_num,",
                    "t2.time_start,",
                    "t2.time_end,",
                    "t2.course_id,",
                    "t2.year_class_id,",
                    "t2.lesson_type,",
                    "t2.full_count,",
                    "(",
                        "SELECT",
                          "count( id ) AS count ",
                        "FROM",
                          "student_grade AS t3 ",
                        "WHERE",
                          "t3.grade_id = t2.id ",
                          "AND t3.deleted = 0 ",
                          "AND t3.`status` &lt;&gt; 2 ",
                        "AND t3.reading_status IN ( 0, 1 )) AS allot_student_count",
                    "FROM",
                    "   grade AS t2 ",
                    "WHERE",
                    "t2.year_part = ${yearPart} ",
                    "AND t2.quarter_num = ${quarterNum} ",
                    "AND t2.school_id = ${schoolId} ",
                    "AND t2.lesson_type = ${lessonType}",
                    "and t2.subjects_id = ${subjectsId} ",
                    "AND t2.`status` =1",
                    "AND t2.deleted = 0 ",
                    "AND t2.is_rebrush = 0 ",
                    "<if test='noonType == 1'>",
                        "and t2.time_start &lt;= '12:00'",
                    "</if>",
                    "<if test='noonType == 2'>",
                        "and t2.time_start &gt; '12:00'",
                        "and t2.time_start &lt;= '18:00'",
                    "</if>",
                    "<if test='noonType == 3'>",
                        "and t2.time_start &gt; '18:00'",
                    "</if>",
                    "<if test='diffType == 1'>",
                        "and t2.time_start &lt; #{schoolTime}",
                    "</if>",
                    "<if test='diffType == 2'>",
                        "and t2.time_start &gt;= #{schoolTime}",
                    "</if>",
                    "<when test='schedulingType!=null and schedulingType!=&apos;&apos; '>",
                        "AND t2.scheduling_type = #{schedulingType} ",
                    "</when>",
                    "<when test='weekDates!=null and weekDates!=&apos;&apos; '>",
                        "AND t2.week_dates = #{weekDates} ",
                        "and t2.week_dates NOT IN (",
                        "SELECT",
                                "g.week_dates ",
                            "FROM",
                                "student_grade AS sg",
                            "LEFT JOIN grade AS g ON sg.grade_id = g.id",
                            "WHERE",
                                "sg.year_part = ${yearPart} ",
                                "AND sg.quarter_num = ${quarterNum} ",
                                "AND sg.subjects_id = ${subjectsId} ",
                                "AND sg.lesson_type IN (${lessonType}) ",
                                "AND sg.student_id = ${studentId} ",
                                "AND sg.deleted = 0 ",
                        ") ",
                    "</when>",
                    "<when test='schoolDateTimeList != null and schoolDateTimeList.size() > 0'> ",
                        "AND (",
                            "<foreach collection='schoolDateTimeList' item='item' open='(' separator='and' close=')'>",
                                "t2.time_end &lt; #{item.timeStart} or t2.time_start &gt; #{item.timeEnd}",
                            "</foreach>",
                        ")",
                    "</when> ",
                    "HAVING((full_count = 0) OR ( allot_student_count &lt; full_count )) ",
                    "ORDER BY t2.time_start ${orderType}",
                    "limit 1",
            "</script>"})
    public Grade getGradeUnClash(@Param("yearPart") Integer yearPart,
                                       @Param("quarterNum") Integer quarterNum,
                                       @Param("schoolId") Long schoolId,
                                       @Param("subjectsId") Long subjectsId,
                                       @Param("lessonType") Integer lessonType,
                                       @Param("schoolDateTimeList") List<SchoolDateTime> schoolDateTimeList,
                                       @Param("noonType") Integer noonType,
                                       @Param("diffType") Integer diffType,
                                       @Param("schoolTime") String schoolTime,
                                       @Param("orderType") String orderType,
                                       @Param("studentId") Long studentId,
                                       @Param("weekDates") String weekDates,
                                       @Param("schedulingType") String schedulingType
    );


    /**
     * 不冲突时间的班级(可排班级)
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param lessonType
     * @param schoolDateTimeList
     * @param orderType asc,desc
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t2.id,",
            "t2.name,",
            "t2.week_dates,",
            "t2.school_id,",
            "t2.subjects_id,",
            "t2.year_part,",
            "t2.quarter_num,",
            "t2.time_start,",
            "t2.time_end,",
            "t2.course_id,",
            "t2.year_class_id,",
            "t2.lesson_type,",
            "t2.full_count,",
            "(",
            "SELECT",
            "count( id ) AS count ",
            "FROM",
            "student_grade AS t3 ",
            "WHERE",
            "t3.grade_id = t2.id ",
            "AND t3.deleted = 0 ",
            "AND t3.`status` &lt;&gt; 2 ",
            "AND t3.reading_status IN ( 0, 1 )) AS allot_student_count",
            "FROM",
            "   grade AS t2 ",
            "WHERE",
            "t2.year_part = ${yearPart} ",
            "AND t2.quarter_num = ${quarterNum} ",
            "AND t2.school_id = ${schoolId} ",
            "AND t2.lesson_type = ${lessonType}",
            "and t2.subjects_id = ${subjectsId} ",
            "AND t2.`status` =1",
            "AND t2.deleted = 0 ",
            "AND t2.is_rebrush = 0 ",
            "and t2.time_end = #{timeEnd} ",
            "and t2.time_start = #{timeStart}",
            "<when test='weekDates!=null and weekDates!=&apos;&apos; '>",
                "AND t2.week_dates = #{weekDates} ",
                "and t2.week_dates NOT IN (",
                "SELECT",
                "g.week_dates ",
                "FROM",
                "student_grade AS sg",
                "LEFT JOIN grade AS g ON sg.grade_id = g.id",
                "WHERE",
                "sg.year_part = ${yearPart} ",
                "AND sg.quarter_num = ${quarterNum} ",
                "AND sg.subjects_id = ${subjectsId} ",
                "AND sg.lesson_type IN (${lessonType}) ",
                "AND sg.student_id = ${studentId} ",
                "AND sg.deleted = 0 ",
            ") ",
            "</when>",
            "<when test='schoolDateTimeList != null and schoolDateTimeList.size() > 0'> ",
            "AND (",
            "<foreach collection='schoolDateTimeList' item='item' open='(' separator='and' close=')'>",
            "t2.time_end &lt; #{item.timeStart} or t2.time_start &gt; #{item.timeEnd}",
            "</foreach>",
            ")",
            "</when> ",
            "HAVING((full_count = 0) OR ( allot_student_count &lt; full_count )) ",
            "ORDER BY t2.time_start ${orderType}",
            "limit 1",
            "</script>"})
    public Grade getGradeUnClashWeekEnd(@Param("yearPart") Integer yearPart,
                                 @Param("quarterNum") Integer quarterNum,
                                 @Param("schoolId") Long schoolId,
                                 @Param("subjectsId") Long subjectsId,
                                 @Param("lessonType") Integer lessonType,
                                 @Param("schoolDateTimeList") List<SchoolDateTime> schoolDateTimeList,
                                 @Param("orderType") String orderType,
                                 @Param("studentId") Long studentId,
                                 @Param("weekDates") String weekDates,
                                 @Param("timeStart") String timeStart,
                                 @Param("timeEnd") String timeEnd
    );


    /**
     * 获取老师带学员使用的课次
     */
    @Select({"<script>",
            "SELECT SUM( t1.use_course_count ) AS use_course_count ",
            "FROM ",
            "student_grade AS t1 ",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_type = 1 ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t2.teacher_id = ${teacherId} ",
            "</when> ",
            "AND t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "GROUP BY t2.teacher_id,t1.student_id ",
            "</script>"})
    public abstract Integer useCourseCountByTeacherId(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("teacherId") Long teacherId);

    /**
     * 查看已排班了几次
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select("select count(id) from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and subjects_id = ${subjectsId} and student_id = ${studentId} and lesson_type = ${lessonType} and deleted =0 and `status`<>3")
    public Integer getYetCount(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType);

    /**
     * 获取老师带学员使用的课次
     */
    @Select({"<script>",
            "SELECT * FROM (",
                "SELECT t1.student_id,t2.school_id,t2.subjects_id,SUM( t1.use_course_count ) AS use_course_count ",
                "FROM ",
                "student_grade AS t1 ",
                "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
                "WHERE ",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND t1.lesson_type = 1 ",
                "AND t2.teacher_id = ${teacherId} ",
                "AND t1.deleted = 0 ",
                "GROUP BY t2.teacher_id,t1.student_id ",
            ") AS t ",
            "WHERE t.use_course_count &gt;= ${courseCount} ",
            "</script>"})
    public abstract List<StudentGrade> useCourseCountByTeacherIdList(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("courseCount") Integer courseCount, @Param("teacherId") Long teacherId);

    /**
     * 获取科目的剩余课时
     */
    @Select({"<script>",
            "SELECT",
            "IFNULL( sum( a.residue_course_count ), 0 ) residue_course_count ",
            "FROM",
            "order_detail AS t1 ",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_type = 1 ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.deleted = 0",
            "</script>"})
    public abstract Integer getResidueCourseCount(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId);

    @Select({"<script>",
            "SELECT",
            "t2.*  ",
            "FROM",
            "student_grade AS t1",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t2.lesson_type = 1 ",
            "AND t2.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "AND t1.is_last = 1 ",
            "</script>"})
    public abstract Grade gradeByStudentId(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId);



}
